from sqlalchemy import (
    create_engine,
    MetaData,
    Table,
    Column,
    Integer,
    String,
    ForeignKey,
    or_,
    and_,
)

from sqlalchemy.orm import relationship, declarative_base
from pprint import pprint

engine = create_engine("mysql+pymysql://root:root@localhost/jobs")
try:
    # 创建声明式的基类，所有的模型类都要继承自这个基类
    Base = declarative_base()

    # 定义User模型类
    class User(Base):
        # 指定数据库的表名为users
        __tablename__ = "users"
        id = Column(Integer, primary_key=True)
        name = Column(String(50))
        fullname = Column(String(50))
        # 定义与Address表一对多的关系，back_populates指定反向引用
        addresses = relationship("Address", back_populates="user")

    class Address(Base):
        # 指定数据库的表名为users
        __tablename__ = "addresses"
        id = Column(Integer, primary_key=True)
        email_address = Column(String(50), nullable=False)
        user_id = Column(Integer, ForeignKey("users.id"))
        user = relationship("User", back_populates="addresses")

    # 在数据库中创建所有的定义的模型对应的表
    Base.metadata.create_all(engine)

except Exception as e:
    print(f"创建数据库表失败:{e}")

# 导入会话创建器
from sqlalchemy.orm import sessionmaker

# 创建会话工厂，绑定上面定义的数据库引擎
Session = sessionmaker(bind=engine)
# 创建会话对象
session = Session()
# 创建一个新的用户
# new_user = User(name="zhang", fullname="zhangsan")
# session.add(new_user)
# session.commit()
# user = session.query(User).filter_by(name="zhang").first()
# new_address1 = Address(email_address="zhangsan1@qq.com", user_id=user.id)
# session.add(new_address1)
# session.commit()
# new_address2 = Address(email_address="zhangsan2@qq.com", user_id=user.id)
# session.add(new_address2)
# session.commit()

# 导入joinedload用于实现立即加载
from sqlalchemy.orm import joinedload

# 查询所有的User对象，并且立即加载其关联的addresses数据列表
# users = session.query(User).options(joinedload(User.addresses)).all()
# for user in users:
#    print(user.fullname)
#    for address in user.addresses:
#        print(address.email_address)


# 采用延迟加载的方式，这个其实是默认的方式
user = session.query(User).first()
# 只有在你访问addresses属性的时候才会去加载关联的addresses数据
addresses = user.addresses
for address in user.addresses:
    print(address.email_address)
